import configparser
from threading import Lock
from typing import List
from datetime import datetime
from mysql.connector import pooling
import calendar

import mysql.connector
import logging

lock = Lock()

class ImageDB:
    """
    用来处理拍摄设备(手机)数据
    """

    def __init__(self, db_config,line_name,  line_token):
        self.config = db_config
        self.line_name = line_name
        self.line_token = line_token
        self.pool:pooling.MySQLConnectionPool = None

    def connect(self):
        try:
            prefix = self.line_token.split("-")[0]
            self.pool = pooling.MySQLConnectionPool(pool_name=f"{prefix}_image_pool", pool_size=8, **self.config)
            self.create_table()
            print("image_pool database connect success")
        except Exception as e:
            print(f"image_pool database connect failed: {e}")
            raise Exception("image_pool 数据库无法连接")

    def get_connection(self):
        return self.pool.get_connection()

    def get_cursor(self, conn, dictionary=None):
        return conn.cursor(dictionary=dictionary)


    def genImageTableName(self):
        """ 生成表名称 """
        current_date = datetime.now()
        year = current_date.year
        month = current_date.month
        # 创建表名
        table_name = f"imgs_{self.line_name}_{year}_{month}"
        return table_name

    def isImageTableExist(self):
        """判断表是否已经存在"""
        conn = self.get_connection()
        with lock:
            cursor = self.get_cursor(conn)
            check_table_query = f"SHOW TABLES LIKE '{self.genImageTableName()}'"
            cursor.execute(check_table_query)
            result = cursor.fetchone()

            cursor.close()
            conn.commit()
            self.close_conn(conn)
            return result is not None

    def define_image_table(self):
        """ 判断是否创建新表 """
        has_table = self.isImageTableExist()
        if has_table is False:
            self.create_table()


    def create_table(self):
        conn = self.get_connection()
        with lock:
            cursor = self.get_cursor(conn)
            # 创建image 图片表
            self.execute(cursor, f'create table if not exists {self.genImageTableName()} ('
                                 'id varchar(64) primary key COMMENT \'主键\' , '
                                 'product_id int DEFAULT NULL COMMENT \'成衣Id\' , '
                                 'device_id int DEFAULT NULL COMMENT \'机位Id\' , '
                                 'area_id int DEFAULT NULL COMMENT \'机位下的区域Id\' , '
                                 'image_name varchar(255) DEFAULT NULL COMMENT \'图片名称\' , '
                                 'image_path varchar(255) DEFAULT NULL COMMENT \'图片相对路径\' , '
                                 'adjust_path varchar(255) DEFAULT NULL COMMENT \'棋盘格图片相对路径\' , '
                                 'creat_time datetime DEFAULT NULL COMMENT \'创建时间\' '
                                 ');')

        cursor.close()
        conn.commit()
        self.close_conn(conn)

    def update_img_other_part(self, pic_id , shoulder_id, left_id, right_id, neck_id):
        """ 更新其他处的图片信息 """
        conn = self.get_connection()
        with lock:
            cursor = self.get_cursor(conn)
            self.execute(cursor, f" UPDATE {self.genImageTableName() }  set product_id = '{pic_id}' where (image_name={shoulder_id} or image_name={left_id} or image_name={right_id} or image_name={neck_id}) and product_id=-1 ")

            cursor.close()
            conn.commit()
            self.close_conn(conn)


    def find_last_adjust_path(self , device_id):
        """ 根据机位ID 获取最近的 棋盘格图片地址 """
        self.define_image_table()
        conn = self.get_connection()
        with lock:
            cursor = self.get_cursor(conn , True)
            self.execute(cursor, f"SELECT adjust_path from { self.genImageTableName() } where device_id = {device_id} and adjust_path is not null and adjust_path <> ''  ORDER BY creat_time DESC limit 1")
            result = cursor.fetchone()

            cursor.close()
            conn.commit()
            self.close_conn(conn)

            if result is None:
                return None
            return result

    def find_img_data(self, device_id, area_id, picture_id):
        """ 根据机位ID和图片ID, 查询已有的图片信息 """
        self.define_image_table()
        conn = self.get_connection()
        with lock:
            cursor = self.get_cursor(conn, True)
            self.execute(cursor, f"SELECT * from {self.genImageTableName()} where device_id = \"{device_id}\" and area_id = \"{area_id}\" and image_name = \"{picture_id}\" and product_id = -1  ORDER BY creat_time DESC limit 1")
            result = cursor.fetchone()

            cursor.close()
            conn.commit()
            self.close_conn(conn)

            if result is None:
                return None
            return result

    def save_img_data(self , data):
        """ 添加图片信息 """
        self.define_image_table()
        conn = self.get_connection()
        with lock:
            cursor = self.get_cursor(conn)
            self.execute(cursor,
                         f'INSERT INTO {self.genImageTableName()} (id, product_id, device_id, area_id, image_name, image_path, adjust_path,  creat_time) '
                         f'VALUES (\'{data["id"]}\', \'{data["product_id"]}\', \'{data["device_id"]}\', \'{data["area_id"]}\', \'{data["image_name"]}\',\'{data["image_path"]}\', \'{data["adjust_path"]}\' , now() )')

            cursor.close()
            conn.commit()
            self.close_conn(conn)


    def update_device_image(self, record_id, all_path):
        """ 更新该机位下所有的图片地址 """
        self.define_image_table()
        conn = self.get_connection()
        with lock:
            cursor = self.get_cursor(conn)
            self.execute(cursor,f" UPDATE {self.genImageTableName()}  set image_path = '{all_path}' where id={record_id} ")

            cursor.close()
            conn.commit()
            self.close_conn(conn)

    def batch_update_device_image_map(self, data):
        """
        批量更新MySQL数据库中的数据。
        :param data: 要更新的数据列表，每个元素是一个字典。
        :param table_name: 数据库表名。
        :param key_columns: 用于定位数据行的键的列名列表。
        :param value_columns: 需要更新的值的列名列表。
        """

        key_columns = ['device_id', 'image_name']
        value_columns = ['product_id']

        self.define_image_table()
        conn = self.get_connection()
        with lock:
            cursor = self.get_cursor(conn)
            # 构建SQL更新语句
            set_clause = ', '.join(f"{col} = %s" for col in value_columns)
            where_clause = ' AND '.join(f"{key} = %s" for key in key_columns)
            sql = f"UPDATE {self.genImageTableName()}  SET {set_clause} WHERE {where_clause} and product_id=-1"

            # 准备批量更新的数据
            update_data = []
            for record in data:
                values = [record[col] for col in value_columns]
                keys = [record[key] for key in key_columns]
                update_data.append(values + keys)

            # 执行批量更新
            cursor.executemany(sql, update_data)

            cursor.close()
            conn.commit()
            self.close_conn(conn)


    def find_pre_product_sleeve_info(self, pid , device_id):
        self.define_image_table()
        conn = self.get_connection()
        with lock:
            cursor = self.get_cursor(conn, True)
            self.execute(cursor, f"SELECT * from {self.genImageTableName()} where device_id = {device_id} and product_id = {pid} ")
            result = cursor.fetchall()

            cursor.close()
            conn.commit()
            self.close_conn(conn)

            if result is None:
                return None
            return result

    def batch_add_device_imgs(self, data):
        """ 批量添加机位中的所有区域的图片 """
        self.define_image_table()
        conn = self.get_connection()
        with lock:
            cursor = self.get_cursor(conn)
            insert_query = f"""
            INSERT INTO {self.genImageTableName()} (id, product_id, device_id, area_id, image_name, image_path, adjust_path,  creat_time) 
            VALUES (%s , %s , %s , %s , %s , %s , %s , now())
            """
            cursor.executemany(insert_query, data)

            cursor.close()
            conn.commit()
            self.close_conn(conn)

        pass


    def close_conn(self , conn):
        if conn is not None:
            conn.close()

    def close(self):
        if self.pool:
            self.pool._remove_connections()

    def execute(self, cursor, sql):
        try:
            cursor.execute(sql)
        except Exception as e:
            self.logger.error(sql)
            self.logger.exception(e)

